# coding=utf-8
from seo.models import Keyword, Group, GroupCategory, GroupKeyword
from settings import db


def getAllProducts():
    return db.sqlQuery("SELECT entity_id FROM firstcatalog_product_entity;")


def createMetaTitle(id):
    result = db.sqlQuery("""SELECT value FROM firstcatalog_product_entity_varchar
                    where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='name'
                    and entity_type_id=4) and entity_id=""" + str(id))
    return result[0][0].replace("\"", "\\\" ").replace("\'", "\\\' ").replace("\n", " ").replace(",", " ")


def getCategoriesIdByProductId(productId):
    categories = db.sqlQuery('SELECT category_id FROM firstcatalog_category_product where product_id=%s;' % productId)
    categoriesId = []
    if categories:
        for category in categories:
            categoriesId.append(category[0])
    return categoriesId


def getKeywordsFromGroupByProductId(id):
    keywords = []
    categoriesId = getCategoriesIdByProductId(id)
    for categoryId in categoriesId:
        groups = GroupCategory.objects.filter(category=categoryId)
        if groups:
            for group in groups:
                groupKeywords = GroupKeyword.objects.filter(group=group)
                for keyword in groupKeywords:
                    keywords.append(keyword.word)
    return keywords


def createMetaKeywords(id):
    keywords = getKeywordsFromGroupByProductId(id)
    if not keywords:
        keywords = Keyword.objects.all()
    result = ""
    name = db.sqlQuery("""SELECT value FROM firstcatalog_product_entity_varchar
                    where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='name' and entity_type_id=4)
                    and entity_id=""" + str(id))
    if name:
        result += name[0][0].replace(",", " ")
        for word in keywords:
            result += ', ' + str(word.word) + ' ' + str(name[0][0].replace(",", " "))
    shortDescription = db.sqlQuery("""SELECT value FROM firstcatalog_product_entity_text
                    where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='short_description' and entity_type_id=4)
                    and entity_id=""" + str(id))
    if shortDescription:
        result += str(", " + shortDescription[0][0].replace(",", " "))
    type = db.sqlQuery("""SELECT value FROM firstcatalog_product_entity_varchar
                    where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='main_type' and entity_type_id=4)
                    and entity_id=""" + str(id))
    if type:
        if type[0][0] is not None:
            result += str(", " + type[0][0].replace(",", " "))
    return result.replace("\"", "\\\" ").replace("\'", "\\\' ").replace("\n", " ")


def createMetaDescription(id):
    result = db.sqlQuery("""SELECT value FROM firstcatalog_product_entity_text
                    where attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='short_description' and entity_type_id=4)
                    and entity_id=""" + str(id))
    return result[0][0].replace("\"", "\\\" ").replace("\'", "\\\' ").replace("\n", " ").replace(",", " ")


def getMeta(product):
    return {"title": createMetaTitle(product[0]), "keywords": createMetaKeywords(product[0]),
            "description": createMetaDescription(product[0])}


def getMetaTitleAttributeId():
    result = db.sqlQuery(
        "SELECT attribute_id FROM firsteav_attribute where attribute_code='meta_title' and entity_type_id=4")
    return result[0][0]


def getMetaKeywordsAttributeId():
    result = db.sqlQuery(
        "SELECT attribute_id FROM firsteav_attribute where attribute_code='meta_keyword' and entity_type_id=4")
    return result[0][0]


def getMetaDescriptionAttributeId():
    result = db.sqlQuery(
        "SELECT attribute_id FROM firsteav_attribute where attribute_code='meta_description' and entity_type_id=4")
    return result[0][0]


def getValueIdTitle(productId):
    result = db.sqlQuery(
        "select value_id from firstcatalog_product_entity_varchar where attribute_id={attributeid} and entity_id={entityid};".format(
            attributeid=str(getMetaTitleAttributeId()), entityid=str(productId)))
    if not result:
        db.sqlQuery('''INSERT INTO `firstcatalog_product_entity_varchar` (`entity_type_id`, `attribute_id`,
            `store_id`, `entity_id`, `value`) VALUES ('4', '%s', '0', '%s', ' ');''' % (str(getMetaTitleAttributeId()),
                    str(productId)))
        db.commit()
        return getValueIdTitle(productId)
    return result[0][0]


def getValueIdKeyword(productId):
    result = db.sqlQuery(
        "select value_id from firstcatalog_product_entity_text where attribute_id={attributeid} and entity_id={entityid};".format(
            attributeid=str(getMetaKeywordsAttributeId()), entityid=str(productId)))
    if not result:
        db.sqlQuery('''INSERT INTO `firstcatalog_product_entity_text` (`entity_type_id`, `attribute_id`,
            `store_id`, `entity_id`, `value`) VALUES ('4', '%s', '0', '%s', ' ');''' % (str(getMetaKeywordsAttributeId()),
                    str(productId)))
        db.commit()
        return getValueIdTitle(productId)
    return result[0][0]


def getValueIdDescription(productId):
    result = db.sqlQuery(
        "select value_id from firstcatalog_product_entity_varchar where attribute_id={attributeid} and entity_id={entityid};".format(
            attributeid=str(getMetaDescriptionAttributeId()), entityid=str(productId)))
    if not result:
        db.sqlQuery('''INSERT INTO `firstcatalog_product_entity_varchar` (`entity_type_id`, `attribute_id`,
            `store_id`, `entity_id`, `value`) VALUES ('4', '%s', '0', '%s', ' ');''' % (str(getMetaDescriptionAttributeId()),
                    str(productId)))
        db.commit()
        return getValueIdTitle(productId)
    return result[0][0]


def setMeta(product, meta):
    db.sqlQuery("""UPDATE firstcatalog_product_entity_varchar set value='""" + meta[
        "title"] + """' where value_id={valueid};""".format(valueid=getValueIdTitle(product[0])))
    db.sqlQuery("""UPDATE firstcatalog_product_entity_text set value='""" + meta[
        "keywords"] + """' where value_id={valueid};""".format(valueid=getValueIdKeyword(product[0])))
    db.sqlQuery("""UPDATE firstcatalog_product_entity_varchar set value='""" + meta[
        "description"] + """' where value_id={valueid};""".format(valueid=getValueIdDescription(product[0])))
    db.commit()


def updateMeta(products):
    for product in products:
        meta = getMeta(product)
        setMeta(product, meta)


def getCategoriesByGroup(group):
    categories = []
    categories_ = GroupCategory.objects.filter(group=group)
    if categories_:
        for category in categories_:
            var = db.sqlQuery("SELECT value FROM firstcatalog_category_entity_varchar where entity_id=%s and "
                              "attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='name' "
                              "and entity_type_id=3);" % category.category)
            if var:
                categories.append({'name': var[0][0], 'id': category.category})
    return categories


def getCategoryNameById(id):
    var = db.sqlQuery("SELECT value FROM firstcatalog_category_entity_varchar where entity_id=%s and "
                      "attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='name' "
                      "and entity_type_id=3);" % id)
    if var:
        return var[0][0]
    return False


def getCategoryIdByName(name):
    var = db.sqlQuery("SELECT entity_id FROM firstcatalog_category_entity_varchar where value='%s' and "
                      "attribute_id=(SELECT attribute_id FROM firsteav_attribute where attribute_code='name' "
                      "and entity_type_id=3);" % name)
    if var:
        return var[0][0]
    return False


def createMETA():
    products = getAllProducts()
    updateMeta(products)
